package com.ruoyi.app.mapper;

import java.util.List;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.ruoyi.app.domain.OrderInfo;
import com.ruoyi.app.domain.OrderProduct;
import com.ruoyi.app.domain.vo.OrderInfoVo;
import com.ruoyi.app.domain.vo.OrderProductVo;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

/**
 * 订单详情Mapper接口
 * 
 * @author zhangkaiqiang
 * @date 2024-10-20
 */
public interface OrderInfoMapper extends BaseMapper<OrderInfo> {

    /**
     * 统计分析
     * @param orderInfoVo
     * @return
     */
    List<OrderInfoVo> statistics(@Param("orderInfoVo") OrderInfoVo orderInfoVo);

    /**
     * 商品分类统计
     * @param orderInfoVo
     * @return
     */
//    @Select("select p.CATEGORY_TXT,SUM(p.TOTAL_SELLING_PRICE) TOTAL_SELLING_PRICE from order_info i INNER JOIN " +
//            "order_product p ON i.ID = p.ORDER_ID AND p.DEL_FLAG = 0 WHERE i.DEL_FLAG = 0 AND i.SHOP_ID = #{orderInfoVo.shopId} " +
//            "AND i.CREATE_TIME BETWEEN #{orderInfoVo.startDate} AND #{orderInfoVo.endDate} GROUP BY p.CATEGORY_TXT")
    @Select("SET @row_index := 0; " +
            "select CATEGORY_TXT,SUM(TOTAL_SELLING_PRICE) TOTAL_SELLING_PRICE,SUM(TOTAL_NUMBER) TOTAL_NUMBER from ( " +
            "select IF(ind > 3, '其他', CATEGORY_TXT) CATEGORY_TXT, TOTAL_SELLING_PRICE, TOTAL_NUMBER from ( " +
            "select p.CATEGORY_TXT,SUM(p.TOTAL_SELLING_PRICE) TOTAL_SELLING_PRICE,SUM(p.TOTAL_NUMBER) TOTAL_NUMBER,(@row_index := @row_index + 1) ind " +
            "from order_info i INNER JOIN order_product p ON i.ID = p.ORDER_ID AND p.DEL_FLAG = 0 " +
            "WHERE i.DEL_FLAG = 0 AND i.SHOP_ID = #{orderInfoVo.shopId} AND i.CREATE_TIME BETWEEN " +
            "#{orderInfoVo.startDate} AND #{orderInfoVo.endDate} GROUP BY p.CATEGORY_TXT ORDER BY TOTAL_SELLING_PRICE DESC " +
            ") a) b GROUP BY CATEGORY_TXT ORDER BY TOTAL_SELLING_PRICE DESC")
    List<OrderProductVo> statisticsCategoryList(@Param("orderInfoVo") OrderInfoVo orderInfoVo);


    /**
     * 统计详情
     * @param orderInfoVo
     * @return
     */
    @Select("select *,(TOTAL_SELL - TOTAL_PRICE) PROFIT, IF(TOTAL_PRICE = 0,0 ,((TOTAL_SELL - TOTAL_PRICE)/TOTAL_SELL*100)) PROFIT_RATE from ( " +
            "select SUM(TOTAL_NUMBER) TOTAL_NUMBER, SUM(TOTAL_SELL) TOTAL_SELL, SUM(TOTAL_PRICE) TOTAL_PRICE from " +
            "order_info WHERE DEL_FLAG = 0 AND SHOP_ID = #{orderInfoVo.shopId} AND CREATE_TIME BETWEEN #{orderInfoVo.startDate} AND #{orderInfoVo.endDate} " +
            " GROUP BY SHOP_ID) a")
    OrderInfoVo statisticsInfo(@Param("orderInfoVo") OrderInfoVo orderInfoVo);

    /**
     * 本月销售金额和数量
     * @param shopId
     * @return
     */
    @Select("select SUM(TOTAL_NUMBER) TOTAL_NUMBER, SUM(TOTAL_SELL) TOTAL_SELL from order_info WHERE DEL_FLAG = 0 " +
            "AND SHOP_ID = #{shopId}  AND DATE_FORMAT(CREATE_TIME, '%Y-%m') = DATE_FORMAT(CURRENT_DATE(), '%Y-%m') GROUP BY SHOP_ID")
    OrderInfoVo currentMonthStatistics(@Param("shopId") String shopId);
}
